﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using ExamSystem.Mdeol;
namespace ExamSystem.DAL
{
   
    public class TpagerstudentDAL
    {
        /// <summary>
        /// 通过试卷id和学生id查询试卷
        /// </summary>
        public Tagerstudent selectpager(int tpid,int tsid)
        {
            Tagerstudent tst = null;
            string sql = "select b.*,a.tpsState,(select SUM(tqScore) from tquestion where tqId in (select tpId from tpagerquestion where tpId=b.tpid)) tpscore,(select COUNT(*) from tpagerquestion where tpId=b.tpid) tpcount,(select SUM(tqscore) from tquestion where tqId in (select tqId from tanswer where tpId=b.tpid and tsId=a.tsid) ) tsscore from tpagerstudent a,tpager b where a.tpId=b.tpId and tsId="+tsid+" and b.tpId="+tpid+"";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tpId = (int)dr["tpid"];
                string tpname = dr["tpname"].ToString();
                string tpbegintime = dr["tpbegintime"].ToString();
                string tpendtime = dr["tpendtime"].ToString();
                int tpscore = dr["tpscore"].ToString() != "" ? (int)dr["tpscore"] : 0;
                int tsscore = dr["tsscore"].ToString() != "" ? (int)dr["tpscore"] : 0;
                int tpcount = (int)dr["tpcount"];
                Tpager t = new Tpager(tpid, tpname, tpbegintime, tpendtime, tpscore, tpcount);
                Tstudent td = new Tstudent();
                td.Tsscore = tsscore;
                tst = new Tagerstudent(0,t,td);
            }
            return tst;
        }





        //查询需要考的学生题
        public List<Tagerstudent> quareytager(string tsno)
        {
            List<Tagerstudent> list = new List<Tagerstudent>();
            string sql = "select b.*,a.tpsState,(select SUM(tqScore) from tquestion) tpscore,(select COUNT(*) from tpagerquestion where tpId=b.tpid) tpcount,(select SUM(tqscore) from tquestion where tqId in (select tqId from tanswer a,tpager b where a.tpId=b.tpid and a.tsId=a.tsid) ) tsscore from tpagerstudent a,tpager b where a.tpId=b.tpId and tsId=(select tsId from tstudent where tsNo='" + tsno + "')";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                int tpid = (int)dr["tpid"];
                string tpname = dr["tpname"].ToString();
                string tpbegintime = dr["tpbegintime"].ToString();
                string tpendtime = dr["tpendtime"].ToString();
                string tpsState = dr["tpsState"].ToString();
                int tpscore = dr["tpscore"].ToString() != "" ? (int)dr["tpscore"] : 0;
                int tsscore = dr["tsscore"].ToString() != "" ? (int)dr["tsscore"] : 0;
                int tpcount = (int)dr["tpcount"];
                Tpager t = new Tpager(tpid, tpname, tpbegintime, tpendtime, tpscore, tpcount);
                Tstudent td = new Tstudent();
                td.Tsscore = tsscore;
                Tagerstudent tst = new Tagerstudent(0, t, td);
                tst.TpsState = int.Parse(tpsState);
                list.Add(tst);
            }
            return list;
        }




        /// <summary>
        ///修改试卷状态
        /// </summary>
        /// <param name="tpid"></param>
        /// <param name="tsid"></param>
        /// <returns></returns>
        public int updatepagerstate(int tpid,int tsid)
        {
            string sql = "update tpagerstudent set tpsState=1 where tpId="+tpid+" and tsId="+tsid+"";
            return DBhepter.ExecuteNonQuery(sql);
        }
    }
}